package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//用于和数据库打交道
//userInfoMapper 则是专门和userinfo进行操作的
public class UserInfoMapper {
    //插入的动作
    public int insert(UserInfo userInfo) {
        try (Connection connection = DBUtil.getConnection()) {
            String sql = "INSERT INTO userinfo (username,password,nickname, age) VALUES (?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, userInfo.getUsername());
            ps.setString(2, userInfo.getPassword());
            ps.setString(3, userInfo.getNickname());
            ps.setInt(4, userInfo.getAge());
            return ps.executeUpdate();//返回增删操作的值 int类型
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
    public UserInfo selectByUsername(String username){
        try(Connection connection = DBUtil.getConnection()){
            String sql = "SELECT username,password,nickname,age FROM userinfo where username=?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,username);
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
                UserInfo userInfo = new UserInfo();
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setNickname(rs.getString("nickname"));
                userInfo.setAge((rs.getInt("age")));
                return userInfo;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    public boolean loginByUsername(String username,String password){
//        try(Connection connection = DBUtil.getConnection()){
//            String sql = "SELECT username,password FROM userinfo WHERE username=? AND password =?";
//            PreparedStatement ps = connection.prepareStatement(sql);
//            ps.setString(1,username);
//            ps.setString(2,password);
//            ResultSet rs = ps.executeQuery();
//            if (rs.next()){
//                return true;
//            }
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return false;
//    }
    public int  updateByUsername(UserInfo userInfo){
        try(Connection connection = DBUtil.getConnection()){
            String sql = "UPDATE userinfo SET password =? , nickname=?,age=? WHERE username=?";
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setString(1, userInfo.getPassword());
            ps.setString(2, userInfo.getNickname());
            ps.setInt(3,userInfo.getAge());
            ps.setString(4, userInfo.getUsername());
            int i = ps.executeUpdate();
            if (i>0){
                return i;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    public List<UserInfo> selectAll(){
        ArrayList<UserInfo> list = new ArrayList<>();
        try(Connection connection = DBUtil.getConnection()){
            String sql = "SELECT * FROM userinfo";
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                UserInfo userInfo = new UserInfo();
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setNickname(rs.getString("nickname"));
                userInfo.setAge(rs.getInt("age"));
                list.add(userInfo);

            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
    public int deleteByUsername(String username){
        try(Connection connection = DBUtil.getConnection()){
            String sql = "DELETE FROM userinfo WHERE username=?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,username);
            int i = ps.executeUpdate();
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
}
